﻿CREATE TABLE [dbo].[TBL_STANDARDDATA]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] VARCHAR(50) NOT NULL, 
    [Description] VARCHAR(255) NOT NULL, 
    [Enabled] BIT NOT NULL DEFAULT 1, 
    [Created] DATETIME NOT NULL DEFAULT getdate(), 
    [Modified] DATETIME NOT NULL DEFAULT getdate()
)

GO

CREATE TRIGGER [dbo].[Trigger_TBL_STANDARDDATA_CREATE]
    ON [dbo].[TBL_STANDARDDATA]
    INSTEAD OF INSERT
    AS
    BEGIN
        INSERT INTO TBL_STANDARDDATA
        SELECT
            Id,
            Name,
            [Description],
            [Enabled],
            CASE WHEN DATEDIFF(MINUTE, Created, GETDATE()) = 0 THEN Created ELSE GETDATE() END AS Created,
            CASE WHEN DATEDIFF(MINUTE, Modified, GETDATE()) = 0 THEN Modified ELSE GETDATE() END AS Modified
        FROM inserted
    END

GO
CREATE TRIGGER [dbo].[Trigger_TBL_STANDARDDATA_UPDATE]
    ON [dbo].[TBL_STANDARDDATA]
    FOR UPDATE
    AS
    BEGIN
        UPDATE EXISTINGRECORD
        SET
            EXISTINGRECORD.Modified = GETDATE()
        FROM TBL_STANDARDDATA AS EXISTINGRECORD
        INNER JOIN inserted AS NEWRECORD ON
            NEWRECORD.Id = EXISTINGRECORD.Id
    END
GO

CREATE UNIQUE INDEX [IX_TBL_STANDARDDATA_name] ON [dbo].[TBL_STANDARDDATA] ([Name]) ON Indexes

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Unique Id',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'TBL_STANDARDDATA',
    @level2type = N'COLUMN',
    @level2name = N'Id'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Name of the record',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'TBL_STANDARDDATA',
    @level2type = N'COLUMN',
    @level2name = N'Name'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Description of the record',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'TBL_STANDARDDATA',
    @level2type = N'COLUMN',
    @level2name = N'Description'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Whether the record is enabled',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'TBL_STANDARDDATA',
    @level2type = N'COLUMN',
    @level2name = N'Enabled'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'When the record was created',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'TBL_STANDARDDATA',
    @level2type = N'COLUMN',
    @level2name = N'Created'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'When the record was last modified',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'TBL_STANDARDDATA',
    @level2type = N'COLUMN',
    @level2name = N'Modified'